"""
Case Type   : VIEW
Case Name   : 查询系统视图pg_stat_activity，定位perform语句嵌套存储过程正在执行的sql
Create At   : 2024/10/8
Owner       : lonely-dance
Description :
    1.创建表
    2.创建perform语句嵌套存储过程
    3.调用存储过程
    4.查询系统视图pg_stat_activity
    5.清理环境，删除存储过程
Expect      :
    1.创建表成功
    2.创建存储过程成功
    3.调用存储过程成功
    4.pg_stat_activity显示调用存储过程正在执行的sql
    5.清理环境成功
History     :
"""
import time
import unittest
import os
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.ComThread import ComThread
from yat.test import Node
from yat.test import macro

primary_sh = CommonSH('PrimaryDbUser')

@unittest.skipIf(3 != primary_sh.get_node_num(), '非1+2环境不执行')
class Analysis(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'---{os.path.basename(__file__)} start---')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby1_sh = CommonSH('Standby1DbUser')
        self.p_node = Node('PrimaryDbUser')
        self.s1_node = Node('Standby1DbUser')
        self.constant = Constant()

    def test_Analysis(self):
        text = '-----step1:创建表数据 expect:成功-----'
        self.log.info(text)
        create_data = '''drop table if exists big_table;
            CREATE TABLE big_table (column1 INT, column2 VARCHAR(100)); 
            INSERT INTO big_table (column1, column2) SELECT generate_series(1, 10000000),
            'data' || generate_series(1, 10000000);'''
        self.log.info(create_data)
        result = self.primary_sh.execut_db_sql(create_data)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result, '执行失败:' + text)
        self.assertIn('INSERT 0 10000000', result, '执行失败:' + text)

        text = '-----step2:创建存储过程 expect:成功-----'
        self.log.info(text)
        create_procedure1 = '''
            CREATE OR REPLACE PROCEDURE test_exec_preform() AS 
            BEGIN 
                PERFORM pg_sleep(10); 
                PERFORM pg_sleep(12); 
            END; 
        '''
        self.log.info(create_procedure1)

        create_procedure2 = '''
            CREATE OR REPLACE PROCEDURE test_exec_preform0004() IS 
            BEGIN 
                PERFORM test_exec_preform(); 
            END; 
        '''
        self.log.info(create_procedure2)
        result1 = self.primary_sh.execut_db_sql(create_procedure1)
        result2 = self.primary_sh.execut_db_sql(create_procedure2)
        self.log.info(result1)
        self.log.info(result2)
        self.assertEqual(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, result1, '执行失败:' + text)
        self.assertEqual(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, result2, '执行失败:' + text)

        text = '-----step3:调用存储过程 expect:成功-----'
        self.log.info(text)
        sql_cmd1 = f'''source {macro.DB_ENV_PATH};
                gsql -d {self.p_node.db_name} -p {self.p_node.db_port} -c "call test_exec_preform0004();";'''
        self.log.info(sql_cmd1)
        sh_thread1 = ComThread(self.p_node.sh, args=(sql_cmd1,))
        sh_thread1.setDaemon(True)
        sh_thread1.start()

        time.sleep(1)
        text = '-----step4:查询pg_stat_activity expect:pg_stat_activity显示调用存储过程正在执行的sql-----'
        self.log.info(text)
        sql_cmd = f'''select * from pg_stat_activity where datname = '{self.p_node.db_name}' ;'''
        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn('test_exec_preform0004()',
                      sql_result, '执行失败:' + text)

        sh_thread1.join(120)

    def tearDown(self):
        text = '-----step5:清理环境 expect:成功-----'
        self.log.info(text)

        sql_cmd = '''
        drop table if exists big_table; 
        drop procedure if exists test_exec_preform; 
        drop procedure if exists test_exec_preform0004;
        '''
        self.log.info(sql_cmd)

        sql_result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertTrue(sql_result.count(self.constant.DROP_PROCEDURE_SUCCESS_MSG) == 2
                        and self.constant.DROP_TABLE_SUCCESS in sql_result, '执行失败:' + text)



